Merveilleux SQL!

AFUP Days Lyon
2019-05-17

Image by Free-Photos from Pixabay

Qui suis-je ?

  • Lætitia Avrot
  • Grande fan de PostgreSQL
  • Membre du CoC PostgreSQL
  • Co-fondatrice de #PostgresWomen
  • @l_avrot
Image by Anemone123 from Pixabay

Les merveilles méconnues du SQL

Image by Dean Moriarty from Pixabay

Différent

  • Langage déclaratif
  • Avec indentation
  • Avec retours à la ligne
  • Avec commentaires
Image by Pascal Laurent from Pixabay

Turing complete!

Démonstration sur le wiki de PostgreSQL: https://wiki.postgresql.org/wiki/
Turing_Machine_(with_recursive)

Advent of code 2017: https://github.com/xocolatl/advent-of-code/tree/master/2017

Image by philcurtis from Pixabay

Historique

  • SQL-86
  • SQL-89 annule et remplace SQL-86
  • SQL-92
  • SQL:1999, SQL:2003, SQL:2006
  • SQL:2008, SQL:2011, SQL:2013
  • SQL:2016

pgexercises.com

Image by Wilfried Pohnke from Pixabay

Fournir la liste des membres du club avec la personne qui les a recommendés (s'ils sont recommendés).

Image by Jill Wellington from Pixabay

Autojointure

(externe)

select
  mem."firstname"
    || ' ' ||
    mem."surname",
  memref."firstname"
    || ' ' ||
    memref."surname"
from
  "members" as mem
left outer join
  "members" as memref
on mem."recommendedby" = memref."memid"
Image by Nisha Gill from Pixabay

Autojointure

  • Jointure d'une table avec elle-même
  • Possible en jointure externe à gauche, à droite et en jointure interne
  • depuis toujours
Image by Nisha Gill from Pixabay

Sous-requête

select
  mem."firstname"
    || ' ' ||
    mem."surname",
  (
    select
      memref."firstname"
        || ' ' ||
        memref."surname"
    from members as memref
    where
      mem."recommendedby" = memref."memid"
  )
from members as mem
Image by sandid from Pixabay

Sous-requête

(dans la clause select)

  • Affiche directement le résultat d'une sous-requête
  • Uniquement pour les sous-requêtes scalaires
  • SQL:1999
Image by sandid from Pixabay
<query specification> ::= SELECT [ <set quantifier> ] <select list> <table expression>

<select list> ::=
<asterisk>
| <select sublist> [ { <comma> <select sublist> }... ]
Image by sandid from Pixabay
<select sublist> ::=
<derived column>
| <qualified asterisk>


<derived column> ::=
<value expression> [ <as clause> ]
Image by sandid from Pixabay
<value expression> ::=
<numeric value expression>
| <string value expression>
| <datetime value expression>
| <interval value expression>
| <boolean value expression>
| <user-defined type value expression>
| <row value expression>
| <reference value expression>
| <collection value expression>
Image by sandid from Pixabay
<reference value expression> ::=
<value expression primary>

<value expression primary> ::= <parenthesized value expression> | <nonparenthesized value expression primary>
Image by sandid from Pixabay
<nonparenthesized value expression primary> ::=
<unsigned value specification>
| <column reference>
| <set function specification>
| <scalar subquery>
| <case expression>
| <cast specification>
| <subtype treatment>
| <attribute or method reference>
| <reference resolution>
| <collection value constructor>
| <routine invocation>
| <field reference>
| <element reference>
| <method invocation>
| <static method invocation>
| <new specification>
Image by sandid from Pixabay
<scalar subquery> ::= <subquery>

<subquery> ::=
<left paren> <query expression> <right paren>


The degree of a <scalar subquery> shall be 1 (one)
Image by sandid from Pixabay

Sous-requête

(dans la clause select)


select
  ( select
      "columnName"
    from tableName
    where ...
  )
            
Image by sandid from Pixabay

CTE

with memref as
(
  select
    "firstname" || ' ' || "surname" as refname
  from
    members
)
select
  mem."firstname" || ' ' || mem."surname",
  memref.refname
from
  members as mem
left outer join memref
on mem."recommendedby" = memref."memid"
Image by Kerstin Riemer from Pixabay

Common Table Expressions (CTE)

  • Permet de déclarer des sous-requêtes
  • Clause with
  • SQL:1999
Image by Kerstin Riemer from Pixabay
<query expression> ::=
[ <with clause> ]
<query expression body>
[ <order by clause> ]
[ <result offset clause> ]
[ <fetch first clause> ]

<with clause> ::=
WITH [ RECURSIVE ] <with list>
Image by Kerstin Riemer from Pixabay
<with list> ::=
<with list element> [ { <comma> <with list element> }... ]

<with list element> ::=
<query name> [ <left paren> <with column list> <right paren> ]
AS <table subquery> [ <search or cycle clause> ]
Image by Kerstin Riemer from Pixabay

Common Table Expressions (CTE)

with "mySubqueryName"
  (
    column1,
    column2,
    ...
  )
as
  (
    whatever subquery
  )
select
from "mySubqueryName"
...
            
Image by Kerstin Riemer from Pixabay
select
  mem."firstname"
    || ' ' ||
    mem."surname",
  memref."firstname"
    || ' ' ||
    memref."surname"
from
  "members" as mem
left outer join
  "members" as memref
on mem."recommendedby" = memref."memid"
Image by succo from Pixabay

select
  mem."firstname",
  mem."surname",
  (
    select
      memref."firstname",
      memref."surname",
    from members as memref
    where
      mem."recommendedby" = memref."memid"
)
from members as mem
Image by succo from Pixabay

Jointure Laterale

select
  mem."firstname",
  mem."surname",
    memref."firstname",
  memref."surname"
from members as mem
left join lateral
  (select "firstname",
     "surname"
   from members as mentors
   where
     mentors.memid = mem.recommendedby) as memref
on true
Image by succo from Pixabay

Jointure Laterale

  • À utiliser lorsqu'on pense "pour chaque ligne, je voudrais ..."
  • Attention à l'addiction!
  • Depuis SQL:1999
Image by succo from Pixabay
<query expression> ::=
[ <with clause> ]
<query expression body>


<query expression body> ::=
<non-join query expression>
| <joined table>
Image by succo from Pixabay
<joined table> ::= <cross join>
| <qualified join>
| <natural join>
| <union join>


<qualified join> ::=
<table reference> [ <join type> ] JOIN <table reference>
<join specification>
Image by succo from Pixabay
<table reference> ::=
<table primary> | <joined table>
Image by succo from Pixabay
<table primary> ::=
<table or query name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <lateral derived table> [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ]
| <collection derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <only spec>
[ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <left paren> <joined table> <right paren>
Image by succo from Pixabay
<lateral derived table> ::=
LATERAL <left paren> <query expression> <right paren>
Image by succo from Pixabay
<join specification> ::=
<join condition>
| <named columns join>


<join condition> ::= ON <search condition>

<search condition> ::=
<boolean value expression>
Image by succo from Pixabay

Fournir la liste des membres du club qui s'appellent David, Tim ou Darren.

Image by Hebi B. from Pixabay

Or

select
  "firstname",
  "surname"
from members
where
  "firstname" = 'David'
or
  "firstname" = 'Tim'
or
  "firstname" = 'Darren'
Image by Hebi B. from Pixabay

In

select
  "firstname",
  "surname"
from members
where
  "firstname" in ('David', 'Tim', 'Darren')
Image by Hebi B. from Pixabay

Values

insert into ("column1",...) values (...);
Image by Couleur from Pixabay

Values

select
  "firstname",
  "surname"
from members
inner join
  values
    (('David'),('Tim'),('Darren'))
  as people(firstname)
on members."firstname" = people."firstname"
Image by Couleur from Pixabay

Values

  • Pour créer une table de constantes
  • Réduire le trafic réseau lors des inserts
  • Optimiser certaines requêtes in
  • Depuis SQL:92
Image by Couleur from Pixabay
<query expression> ::=
<non-join query expression>
| <joined table>


<non-join query expression> ::=
<non-join query term>
| <query expression> UNION [ ALL ] [ <corresponding spec> ] <query term>
| <query expression> EXCEPT [ ALL ] [ <corresponding spec> ] <query term>
Image by Couleur from Pixabay
<non-join query term> ::=
<non-join query primary>
| <query term> INTERSECT [ ALL ] [ <corresponding spec> ] <query primary>


<non-join query primary> ::=
<simple table>
| <left paren> <non-join query expression> <right paren>
Image by Couleur from Pixabay
<simple table> ::=
<query specification>
| <table value constructor> | <explicit table>


<table value constructor> ::=
VALUES <table value constructor list>
Image by Couleur from Pixabay

Fournir la liste des membres du club qui n'ont jamais réservé d'équipement.

Image by Terri Cnudde from Pixabay

NOT IN

select surname,
  firstname
from members
where "memid" not in
  (
    select memid
    from bookings
  )
Image by Terri Cnudde from Pixabay

NOT EXISTS

select surname,
  firstname
from members
where not exists
  (
    select 1
    from bookings
    where bookings.memid = members.memid
  )
Image by Terri Cnudde from Pixabay

ANTI-JOIN

select surname,
  firstname
from members
left outer join "bookings"
on "members"."memid" = "bookings"."memid"
where "bookings".memid is null
Image by Catkin from Pixabay

Pour l'année 2012, fournir la durée de réservation par mois pour chaque équipement, puis le total sur l'année pour chaque équipement, puis le total pour tous les équipements

Image by Steve Buissinne from Pixabay

3 requêtes

select "facid",
  extract(month from starttime), sum(slots)
from "bookings"
where extract(year from starttime) = 2012
group by factid,
  extract(month from starttime)
union
select "facid",
  null, sum(slots)
from "bookings"
where extract(year from starttime) = 2012
group by factid 
union
select null,
  null, sum(slots)
from "bookings"
where extract(year from starttime) = 2012

Rollup

select facid,
  extract(month from starttime),
  sum(slots) as slots
from cd.bookings
where extract(year from starttime) = 2012
group by rollup("facid", extract(month from "starttime"))
order by "facid",
  extract(month from "starttime")

Rollup

  • Données hiérarchisées
  • Différent de CUBE
  • Depuis SQL:99
<group by clause> ::=
GROUP BY <grouping specification>


<grouping specification> ::=
<grouping column reference>
| <rollup list>
| <cube list>
| <grouping sets list>
| <grand total>
| <concatenated grouping>
<rollup list> ::=
ROLLUP <left paren> <grouping column reference list> <right paren>

Fournir le nombre total de membres du club ainsi que les noms et prénoms de chaque membre (par ordre de date d'adhésion).

Image by Pexels from Pixabay

Sous-requête

select (
  select count(*) from members
) as count,
firstname, surname
	from members
order by joindate
Image by sandid from Pixabay

Window functions

select count(*) 
  over(),
  "firstname",
  "surname"
from members
order by joindate 
Image by RD LH from Pixabay

Window functions

  • Aggrégats en dehors des colonnes affichées
  • SQL:2003
  • SQL:2011: LEAD, LAG...
Image by RD LH from Pixabay
<window function> ::= <window function type> OVER <window name or specification>

<window name or specification> ::=
<window name>
| <in-line window specification>


<in-line window specification> ::= <window specification>
Image by RD LH from Pixabay
<window specification> ::=
<left paren> <window specification details> <right paren>


<window specification details> ::=
[ <existing window name> ]
[ <window partition clause> ]
[ <window order clause> ]
[ <window frame clause> ]
Image by RD LH from Pixabay

Pour le membre d'id 27, fournir les noms/prénoms de tous les membres qui l'ont recommandé (directement ou indirectement)

Image by Free-Photos from Pixabay

CTE récursive

with recursive
recommenders(recommender) as (
  select recommendedby from cd.members where memid = 27
  union all
  select mems.recommendedby
  from recommenders recs
  inner join cd.members mems
    on mems.memid = recs.recommender
)
select recs.recommender, mems.firstname, mems.surname
from recommenders recs
inner join cd.members mems
  on recs.recommender = mems.memid
order by memid desc 
Image by andreas N from Pixabay

Conclusion